{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取Excel生成PDF报告"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 引入相关包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "\n",
    "# 安装方法：pip install reportlab\n",
    "from reportlab.pdfbase import pdfmetrics\n",
    "from reportlab.pdfbase.ttfonts import TTFont\n",
    "from reportlab.lib.styles import getSampleStyleSheet,ParagraphStyle\n",
    "from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer,Image,Table,TableStyle\n",
    "from reportlab.lib import colors\n",
    "from reportlab.lib.pagesizes import A4\n",
    "from reportlab.lib.units import inch\n",
    "\n",
    "### 设置中文字体名称为微软雅黑，msyh.ttf需要自己指定文件路径\n",
    "pdfmetrics.registerFont(TTFont('msyh', 'msyh.ttf'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 数据统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"./数据-学生成绩表.xlsx\", index_col='姓名')\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-1-df0c1a04fef9>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# 描述性统计\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m df_desc = (\n\u001b[0;32m----> 3\u001b[0;31m     \u001b[0mdf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m     \u001b[0;34m.\u001b[0m\u001b[0mdescribe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0;34m.\u001b[0m\u001b[0mapplymap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mlambda\u001b[0m \u001b[0mx\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0mround\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mx\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "# 描述性统计\n",
    "df_desc = (\n",
    "    df\n",
    "    .describe()\n",
    "    .applymap(lambda x : round(x, 2))\n",
    "    .reset_index()\n",
    "    .rename(columns={'index':'统计项'})\n",
    ")\n",
    "df_desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 输出统计图\n",
    "df.plot.hist(bins=20, subplots=True)\n",
    "plt.savefig('plot.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 开始构建PDF，添加标题文字"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 容纳所有的PDF的内容\n",
    "elements = []\n",
    "\n",
    "# 读取reportlab定义好的样式表\n",
    "style = getSampleStyleSheet()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 大标题\n",
    "title = \"\"\" <para> <font face=\"msyh\"> 考生成绩统计报告 </font> </para>\"\"\"\n",
    "elements.append(Paragraph(title, style['Heading1']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 添加表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 转换成数组\n",
    "table_data  = [list(df_desc.columns)] + df_desc.to_numpy().tolist()\n",
    "table_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 小标题\n",
    "title = \"\"\" <para> <font face=\"msyh\"> 统计明细 </font> </para>\"\"\"\n",
    "elements.append(Paragraph(title, style['Heading2']))\n",
    "\n",
    "# 表格对象\n",
    "mytable = Table(table_data)\n",
    "mytab_style = TableStyle([\n",
    "    ('GRID', (0,0), (-1,-1), 1, colors.black),\n",
    "    ('FONTSIZE', (0,0), (-1,-1), 12),\n",
    "    ('ALIGN', (0,-1), (-1,-1), 'LEFT'),\n",
    "    ('FONT', (0,0), (-1,-1), 'msyh')\n",
    "])\n",
    "mytable.setStyle(mytab_style)\n",
    "\n",
    "# 添加对象\n",
    "elements.append(mytable)\n",
    "elements.append(Spacer(1, 0.2*inch))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. 添加图片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加一个小标题\n",
    "elements.append(Paragraph(\"\"\"<para><font face=\"msyh\">成绩分布</font></para>\"\"\", style[\"Heading2\"]))\n",
    "\n",
    "# 添加图片\n",
    "img = Image('plot.png')\n",
    "elements.append(img)\n",
    "elements.append(Spacer(1, 0.2*inch))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. 生成PDF文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "doc = SimpleDocTemplate(\"学生成绩统计报告.pdf\", pagesize=(A4[0], A4[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "doc.build(elements)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
